Remote Data Services

In a previous example, I showed you how a DHTML application can use ADO to perform a search on an MDB database and display the results as a table in an HTML page. When creating real-world Internet applications, however, you obviously can't use the approach used in that example because the database isn't local and you don't have a path to it.

Another problem that you must solve when a client communicates with a Web browser is that HTTP is a stateless protocol, which means that no information is retained between consecutive requests from the browser. This sharply contrasts with the ADO way of doing things, which in general expects that the client is always in touch with the data source, from its logon and until the connection is closed. The ADO objects that get closer to the concept of a connectionless state are disconnected Recordsets, which update data through optimistic batch updates.

How can you read data from and write data to a database located on a remote Web server? The answer to this question is provided by Remote Data Services (RDS). You can choose one of two ways to use such objects: You can use bound DHTML controls or "pure" ADO code.

The simplest way to display data coming from a data source on an HTML page is to place an RDS.DataControl object on the page and bind one or more controls to it. This is conceptually similar to having bound controls on a regular Visual Basic form, but the actions you have to undertake are different.

The first thing you have to do is add a RDS.DataControl to the HTML page. This object is an ActiveX component exposed by the RDS library, and you can place it on an HTML page with the following <OBJECT> tag in the body of the page:

<OBJECT CLASSID=clsid:BD96C556-65A3-11D0-983A-00C04FC29E33 ID=dcPublishers HEIGHT=1 WIDTH=1> <PARAM NAME="Server" VALUE="http://www.yourserver.com"> <PARAM NAME="Connect" VALUE="DSN=Pubs"> <PARAM NAME="SQL" VALUE="SELECT * FROM Publishers"> </OBJECT>

You must set at least three properties of the RDS.DataControl object: The Server property is the URL of the server where the data source resides, the Connect property points to the data source on that server, and SQL is the text of the query. You can also create the RDS.DataControl dynamically, which is especially useful when you want to assign these properties at run time, when the page has been already loaded. You can dynamically create a RDS.DataControl object using plain VBScript code placed in the Window_onload event or outside any VBScript procedure:

<SCRIPT LANGUAGE="VBScript" ' This code executes when the page loads. Dim dcPublishers Set dcPublishers = CreateObject("RDS.DataControl") dcPublishers.Server = "http://www.yourserver.com" dcPublishers.Connect = "DSN=Pubs" dcCustomer.SQL = "SELECT * From Publishers" dcCustomer.Refresh </SCRIPT>

The Server property can point to an HTTP URL address or to an HTTPS URL address for a secure protocol (HTTPS is the Secure Hypertext Transfer Protocol). In both cases, the URL can include a port number. If you're retrieving data through DCOM, you can assign the name of the machine where the data source is. Finally, if you're working with a local database (typically, during earlier debug phases) you can assign an empty string to this property or omit it in the <OBJECT> tab. If you don't specify the server, the RDS.DataControl object is instantiated as an in-process object. All the demonstration applications on the companion CD use a local NWind.mdb, so this property is always left blank. Remember to assign it a meaningful value when you move the application to your local network or intranet.

You can bind many different types of DHTML elements to a RDS.DataControl object, some of which are listed in Table 19-1. All bindable elements support three properties:

• DATASRC is the name of the RDS.DataControl the element is bound to, preceded by a # sign, for example, #dcPublishers. (It corresponds to the DataSource property of a Visual Basic bound control.)

• DATAFLD is the name of the field in the data source this element binds to. (It corresponds to the DataField Visual Basic's property.)

• DATAFORMATAS can be text or HTML, depending on whether the contents of the source field must be interpreted as plain text or HTML code. The default for this property is text, and you can use HTML only for the controls that support the innerHTML property.

Here's an example of TextBox controls that are bound to the dcPublishers RDS.DataControl created previously:

Publisher Name: <BR> <INPUT ID="txtPubName" DATAsrc="#dcpublishers" DATAFLD="Pub_Name"><BR> City: <BR> <INPUT ID="txtCity" DATAsrc="#dcpublishers" DATAFLD="City"><BR>

Table 19-1. Some of the HTML elements that can be bound to an RDS.DataControl object.

Element	Bound Property	Updatable
A	href	No
BUTTON	innerText/innerHTML	Yes
DIV	innerText/innerHTML	Yes
IMG	src	No
INPUT	value or checked (depending on the TYPE attribute)	Yes
SELECT	the text of the selected OPTION tag	Yes
SPAN	innerText/innerHTML	Yes
TEXTAREA	value	Yes

Unlike the standard ADO Data control, the RDS.DataControl object doesn't have a visible interface, so you must provide the buttons for navigating the Recordset. Such buttons use the methods of the Recordset exposed by the RDS.DataControl object. This VBScript code assumes that you've created the four btnMovexxxx buttons, plus the btnDelete and the btnAddNew controls:

Sub btnMoveFirst_onclick() dcPublishers.Recordset.MoveFirst End Sub Sub btnMovePrevious_onclick() dcPublishers.Recordset.MovePrevious If dcPublishers.Recordset.BOF Then dcPublishers.Recordset.MoveFirst End Sub Sub btnMoveNext_onclick() dcPublishers.Recordset.MoveNext If dcPublishers.Recordset.EOF Then dcPublishers.Recordset.MoveLast End Sub Sub btnMoveLast_onclick() dcPublishers.Recordset.MoveLast End Sub Sub btnDelete_onclick() dcPublishers.Recordset.Delete dcPublishers.Recordset.MoveNext If dcPublishers.Recordset.EOF Then dcPublishers.Recordset.MoveLast End Sub Sub btnAddNew_onclick() dcPublishers.Recordset.AddNew End Sub

The RDS.DataControl object works with disconnected Recordsets, so all the changes you make to it through bound controls are cached locally. When you're ready to send the changes to the data source, you execute the RDS.DataControl's SubmitChanges method. You typically invoke this method in the Window_onunload event or from the onclick event of a button:

Sub btnUpdate_onclick() dcPublishers.SubmitChanges End Sub

You can cancel all pending updates using the CancelUpdate method. On the companion CD, you'll find an application that uses bound HTML controls to connect to the Customers table of a local copy of NWind.mdb; you'll probably have to change the Connect property of the RDS.DataControl to have it point to a valid path on your system.

All bound controls can raise two events, which you can trap from a script in the page or from Visual Basic code in a DHTML application. The onbeforeupdate event fires before a modified value is transferred from the control to the data source; if you don't cancel it, the control fires an onafterupdate event immediately after the update operation finishes executing. You can use these events to validate the data that the user has entered in bound controls, as you can see in Figure 19-17.

Figure 19-17. This DHTML application uses bound controls and some VBScript code behind the navigational buttons.

If you prefer displaying the result of a query in tabular format, you can take advantage of the special binding features of the DHTML tables. In this case, you have to assign the DATASRC property in the <TABLE> tag and then prepare one single row of table cells containing <SPAN> tags with appropriate DATAFLD attributes. The following code is taken from the demonstration program (shown in Figure 19-18) provided on the companion CD:

<TABLE DATAsrc="#dccustomers" BORDER=1> <THEAD><TR> <TH>Company Name</TH> <TH>Address</TH> <TH>City</TH> <TH>Region</TH> <TH>Country</TH> </TR></THEAD> <TBODY><TR> <TD><B><SPAN DATAFLD="CompanyName"></SPAN><B></TD> <TD><SPAN DATAFLD="Address"></SPAN></TD> <TD><SPAN DATAFLD="City"></SPAN></TD> <TD><SPAN DATAFLD="Region"></SPAN></TD> <TD><SPAN DATAFLD="Country"></SPAN></TD> </TR> </TBODY> </TABLE>

For each record in the source, the RDS.DataControl object generates a new row of cells. For such dynamically generated rows, RDS.DataControl uses the HTML template included between the <TBODY> and </TBODY> tags. You can format and align individual columns by using standard HTML tags. For example, the sample application displays the CompanyName field in boldface.

Figure 19-18. Bound DHTML tables automatically resize their columns to display the contents of their cell in the most appropriate way.

The RDS.DataControl object exposes several other properties and methods that can be used for fine-tuning your application. For example, the InternetTimeout property gives you the timeout in milliseconds for HTTP transmissions, while the SortColumn and the SortDirection properties let you sort data in the underlying Recordset:

' Sort on the City field in ascending order. dcPublishers.SortDirection = True dcPublishers.SortColumn = "City" dcPublishers.Reset

The FilterColumn, FilterCriterion, and FilterValue properties work together to apply a filter on the retrieved data:

' Display only U.S. publishers. dcPublishers.FilterColumn = "Country" ' FilterCriterion supports the following operators: < <= > >= = <>. dcPublishers.FilterCriterion = "=" dcPublishers.FilterValue = "USA" dcPublishers.Reset

By default, the RDS.DataControl executes the query and fetches the Recordset asynchronously. You can control how queries are executed using the ExecuteOptions property, which can be 1-adcExecSync or 2-adcExecAsync. Similarly, you can determine how the Recordset is fetched using the FetchOptions property, which can take one of the following values: 1-adcFetchUpFront (synchronous execution, the control is returned to the application when the Recordset has been completely populated); 2-adcFetchBackground (the control is returned to the application when the first batch of records is returned, and the remaining data is retrieved asynchronously); or 3adcFetchAsync (the default mode, all the records are retrieved in the background).

When the RDS.DataControl is working asynchronously, you must test the ReadyState property, which returns one of the following values: 2-adcReadyStateLoaded (the Recordset is open but no data has been retrieved yet); 3-adcReadyStateInteractive (the Recordset is being populated); 4-adcReadyStateComplete (the Recordset has completed retrieving data). When this property receives a new value, the RDS.DataControl fires an onreadystatechange event. You can cancel an asynchronous operation using the Cancel method.

When an error occurs and no VBScript code is executing, the RDS.DataControl object raises an onerror event.

While binding is always great for creating a prototype, in most cases you must write code if you want to remain in control of the whole process. The RDS library comprises a few objects that make it possible for a disconnected client to exchange data using a stateless protocol. More precisely, when developing applications based on RDS you use objects from three different libraries. (See Figure 19-19.)

• RDS.DataSpace is a component that runs in the client application and represents a link to the server on which the data actually resides. This object is exposed by the Microsoft Remote Data Services library (Msadco.dll).

• RDSServer.DataFactory is a component that runs on the server. It queries the data source and optionally updates it with data coming from the client. This object is exposed by the Microsoft Remote Data Services Server library (Msadcf.dll). You don't need to install this library on client workstations.

• RDS.DataControl (described in the previous section) is an ActiveX component that you can drop on an HTML page. It allows you to bind one or more elements on the page to the remote data source. This object is included in the Msadco.dll library and encompasses the functionality in both RDS.DataSpace and RDSServer.DataFactory.

• ADOR.Recordset is functionally similar to a regular ADO Recordset, but it takes fewer resources and is therefore a better choice when the application runs inside the browser and doesn't need the full power and versatility of ADO. This object is exposed by the Microsoft ActiveX Data Object Recordset library (Msador15.dll). The ADOR library also includes the Field and the Property objects, but not the Connection and Command objects. This library is automatically installed with Internet Explorer, so it never requires you to download and install it on the client workstation.

Figure 19-19. All the objects that partake of a typical RDS session.

If you're accustomed to the ADO way of doing things, the approach you must follow with RDS to establish a connection might seem at first unnatural and unnecessarily complex. But it has its inner logic and also offers a lot of flexibility.

Before trying the code that follows in the Visual Basic IDE, add a reference to the RDS and the ADOR libraries in the References dialog box. The next step is to create an instance of the RDS.DataSource object and use its CreateObject method to create an instance of the remote RDSServer.DataFactory object. You don't need to add a reference to the RDSServer library because you're going to assign the return value of the CreateObject method to a generic Object variable.

Dim ds As New RDS.DataSpace Dim df As Object Set df = ds.CreateObject("RDSServer.DataFactory", _ "http://www.yourserver.com")

The second argument of the RDS.DataSpace's CreateObject method can be an HTTP or HTTPS URL address, the name of another computer in the network, or an empty string if you're instantiating a DataFactory object on the same machine on which the program is running.

After you've obtained a reference to a valid RDSServer.DataFactory object, you can use its Query method to actually retrieve the Recordset object that contains the result of the query:

Dim rs As ADOR.Recordset Set rs = df.Query("DSN=Pubs", "SELECT * FROM Publishers")

The first argument of the Query method is the connection string that the DataFactory object will use to connect to the data source, so you can use all the arguments you would use for the ConnectionString property of an ADO.Connection object. Don't forget that this connection string will be used by a component that already runs on the server (so you don't need a lengthy timeout value), and ensure that you're referring to a DSN or other connection attributes that are valid for that particular server.

You can use the ADOR.Recordset object as you would use a regular ADO Recordset because the differences between the two objects are minimal. (See ADO documentation for more details.) You can navigate the Recordset and update its fields, but all your changes are cached locally. Because you don't have bound controls, you must provide the code that moves data to and from the Recordset and the fields in the page. It turns out that you can take advantage of the dataFld property even when the control isn't bound to a data source. In fact, you can assign the name of the field that you want to display in the control to this property and then move data back and forth using the following routines:

' You can reuse these routines in any DHTMLPage module. Sub GetFieldData() ' Move data from the Recordset to the fields in the page. ' All the "pseudo-bound" controls have a nonempty DataFld property, ' so you just need to iterate on the "all" collection. Dim ctrl As Object On Error Resume Next For Each ctrl In Document.All If Len(ctrl.dataFld) = 0 Then ' Empty or unsupported DataFld property. Else ' Append an empty string to account for Null values. ctrl.Value = rs(ctrl.dataFld) & "" End If Next End Sub Sub PutFieldData() ' Move data from the fields in the page to the Recordset. Dim ctrl As Object On Error Resume Next For Each ctrl In Document.All If Len(ctrl.dataFld) = 0 Then ' Empty or unsupported DataFld property. ElseIf rs(ctrl.dataFld) & "" <> ctrl.Value Then ' Don't update the Recordset if it isn't necessary. rs(ctrl.dataFld) = ctrl.Value End If Next End Sub

Thanks to these routines, it's easy to write the code associated with navigational buttons. For example, this is the code that executes when the user clicks on the Next button:

Private Function btnMoveNext_onclick() As Boolean PutFieldData ' Save current values. rs.MoveNext ' Move to the next record. If rs.EOF Then rs.MoveLast ' Go back if you moved too far. GetFieldData ' Display the current record. End Function

When you're ready to submit changes to the server, you must invoke the SubmitChanges method of the RDSServer.DataFactory object. This method expects the connection string and a reference to the Recordset that must be marshaled back to the data source:

' Specify that you want to marshal only modified values. rs.MarshalOptions = adMarshalModifiedOnly ' Send modified values to the server. df.SubmitChanges conn, rs

The SubmitChanges method fails if there were conflicts in just one single record. In this circumstance, the RDS library is far less sophisticated than the ADO library because in the ADO library you can manage conflicts on a record-by-record basis.

Remote Data Services technology promises much more than a way to move a Recordset back and forth between the server and the client. In fact, the DataSource's CreateObject method can instantiate any ActiveX component that resides on the Web server. In a sense, you might consider RDS the extension of DCOM to the HTTP and HTTPS protocols. This new technique opens up a new world of opportunities for the brave programmer.

From this perspective, the RDSServer.DataFactory object is just one of the many components that can be instantiated on the Web server and deserves special attention only because it's provided in the RDS package. But when you dive into the production of real world applications, you see that this component has one defect: Once the client has created a link to the server, it can query any database on that server, provided that it has a correct user name and password. In fact, using a trial-and-error approach, a client equipped this way can discover user names and passwords it doesn't have. This security scheme is inadequate for a Web server, which is exposed to attack from any browser in the world.

The solution to the security problem is to build a custom ActiveX component and install it on the Web server. Such a component can exposeùthrough its properties and methodsùonly the data that you want to make available to the outside. Additionally, because client workstations access the database through this custom component, you have all the benefits of a three-tier architecture:

• The code in client applications is simplified because the custom component can expose higher level methods that access and process data.

• The clients never see the physical structure of the database on the server, so you can change the implementation of the database without worrying about any adverse impact on clients.

• The component can process data locally before returning a result to a client, which often makes for a better overall performance.

A custom component intended to be instantiated via a RDS.DataSpace's CreateObject method isn't really different from a regular ActiveX component, so you can make use of all you've learned in Chapter 16. The component should expose methods that enable the client to execute a query and send new and updated records back to the component.

On the companion CD, you'll find a simple ActiveX DLL component named NWindFactory.Shipper. This component lets a Web client query the Shippers table in the NWind.mdb database installed on the server computer. The component exposes just three methods: GetShippers returns a disconnected ADOR.Recordset with all the records in the Shippers table, UpdateShippers updates the table with values from the ADOR.Recordset passed to it in its argument, and GetEmptyShippers returns an empty ADOR.Recordset that the client can use to insert information about new shippers. This is the complete source code of the component:

' This is the path of the NWind.mdb database on the server. Const DBPATH = "C:\Program Files\Microsoft Visual Studio\Vb98\NWind.mdb" Dim conn As String Private Sub Class_Initialize() ' Initialize the connection string. conn = "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=" & DBPATH End Sub ' Return the Shippers table in a Recordset object. Function GetShippers() As ADOR.Recordset Dim rs As New ADOR.Recordset ' Query the Shippers table. rs.CursorLocation = adUseClient rs.Open "SELECT * FROM Shippers", conn, adOpenStatic, _ adLockBatchOptimistic ' Disconnect the Recordset. Set rs.ActiveConnection = Nothing Set GetShippers = rs End Function ' Update the Shippers table with data contained in a Recordset. Function UpdateShippers(rs As ADOR.Recordset) As Boolean On Error Resume Next rs.ActiveConnection = conn ' Reconnect the Recordset. rs.UpdateBatch ' Perform the updates. Set rs.ActiveConnection = Nothing ' Disconnect it once again. UpdateShippers = (Err = 0) ' Return True if everything is OK. End Function ' Return an empty Recordset. Function GetEmptyShippers() As ADOR.Recordset Dim rs As New ADOR.Recordset ' Retrieve an empty Recordset from the Shippers table. rs.CursorLocation = adUseClient ' Notice the WHERE clause in the following SQL SELECT command. rs.Open "SELECT * FROM Shippers WHERE 0", conn, adOpenStatic, _ adLockBatchOptimistic ' Disconnect the Recordset. Set rs.ActiveConnection = Nothing Set GetEmptyShippers = rs End Function

For better results, you should compile your custom ActiveX DLL components using the Unattended Execution option and the Apartment Threading model. Both these options are in the General tab of the Project Properties dialog box.

To make the ActiveX custom component available for installation through RDS, you have to take one more step. Not all the components installed on the server can be instantiated from an Internet client because it would be very difficult to enforce decent security. Only the components that are listed under a given key in the server's Registry can be instantiated through RDS. More precisely, you must create the following key in the server's Registry:

HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\W3SVC\Parameters\ ADCLaunch\<servername.classname>

Notice that this key has no value. Moreover, a component for RDS should be marked as Safe For Scripting and Safe For Initialization, which means adding two more keys to the Registry, as explained in the section "Component Download" in Chapter 17.

When you're building the installation package of a component, the best way to go is to prepare a REG file that patches the Registry automatically. For example, the next code snippet is the REG file for the sample NWindFactory.Shippers component. The first entry marks the component as an object that can be instantiated through the RDS.DataSpace's CreateObject method, whereas the remaining two entries mark it with the Safe For Scripting and Safe For Initialization settings. When you create a REG file for your own component, you have to substitute the "NWindFactory.Shippers" string with the component's ProgID, and the string {03C410F7-C7FD-11D2-BAC5-0080C8F21830} with the component's CLSID.

REGEDIT4 [HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\W3SVC\Parameters\ ADCLaunch\NWindFactory.Shippers] [HKEY_CLASSES_ROOT\CLSID\{03C410F7-C7FD-11D2-BAC5-0080C8F21830}\ Implemented Categories\{7DD95801-9882-11CF-9FA9-00AA006C42C4}] [HKEY_CLASSES_ROOT\CLSID\{03C410F7-C7FD-11D2-BAC5-0080C8F21830}\ Implemented Categories\{7DD95802-9882-11CF-9FA9-00AA006C42C4}]

You don't need to create a key for the RDSServer.DataFactory object because the Registry patching is part of the RDS installation package on the server.

Using a custom component via RDS is similar to using the RDSServer.DataFactory object. You just have to create an instance of your component through the RDS.DataSpace object's CreateObject and then use your component's methods to retrieve and update the Recordset. Because your clients never have to directly perform queries on the database, they simply need to reference the ADOR lightweight library instead of the full-fledged ADO library.

Figure 19-20 shows the demonstration client application. Its three TextBox controls are dynamically bound to the Recordset retrieved from the component. This is a partial listing of the code in the main form of the application:

' Modify this constant to point to your Web server, ' or use an empty string to connect to a local component. Const WEB_SERVER = "www.yourserver.com" Dim ds As New RDS.DataSpace Dim myObj As Object Dim rs As ADOR.Recordset Private Sub Form_Load() ' Create the remote component. Set myObj = ds.CreateObject("NWindFactory.Shippers", WEB_SERVER) End Sub Private Sub cmdGetShippers_Click() ' Ask the component to query the table and then return a Recordset. Set rs = myObj.GetShippers() ' Bind the controls to this Recordset. SetDataSource rs End Sub Private Sub cmdGetEmptyShippers_Click() ' Ask the component to create an empty Recordset. Set rs = myObj.GetEmptyShippers() ' Bind the controls to this Recordset. SetDataSource rs End Sub Private Sub cmdUpdateShippers_Click() ' This optimizes the update operation. rs.MarshalOptions = adMarshalModifiedOnly ' Pass the updated Recordset to the component, and test the result. If myObj.UpdateShippers(rs) Then MsgBox "Update successful", vbExclamation Else MsgBox "Unable to update!", vbCritical End If End Sub Sub SetDataSource(obj As Object) ' Use the Recordset as a data source for the fields. Set txtShipperID.DataSource = obj Set txtCompanyName.DataSource = obj Set txtPhone.DataSource = obj End Sub

Figure 19-20. A demonstration application using dynamically bound controls.

You can optimize the update process using the Recordset's MarshalOptions property; if you set this property to 1-adMarshalModifiedOnly, only the records that have been modified, added, or deleted are transferred back to the server. If the update operation fails, you can figure out what happened by checking the Status property for each record in the Recordset. For all the records that weren't successfully updated, this property returns a value different from adRecUnmodified.